/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.pay.bean;


import com.yunhoo.asset.util.PayCodeConstants;
import com.yunhoo.pay.db.impl.CommonOperationImpl;
import com.yunhoo.pay.model.Expense;
import com.yunhoo.pay.model.QueryVouchers;
import com.yunhoo.pay.model.Stit;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.FacesUtils;
import com.yunhoo.sys.util.common.LoginParam;
import com.yunhoo.sys.util.common.NavigationTool;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.richfaces.component.UIDataTable;

/**
 *
 * @author Administrator
 */
@ManagedBean(name ="stit")
@SessionScoped
public class StitBean {

    private List<Expense> statlist = new ArrayList();
    private DataModel statModel = new ListDataModel();
    private List<Expense> totalByPsn = new ArrayList();
    private Stit stit = new Stit();
    private UIDataTable stitData = null;
    private List<String> voutypes = new ArrayList();    
    private QueryVouchers qlb = new QueryVouchers();
    private Expense exp = new Expense();
    public StitBean() {
        //statlist = IbatisTemplate.queryForList("queryAllVouchers", login.getPer().getCpsn_number().trim());
        //stit = (Stit) IbatisTemplate.query("stitForPer", login.getPer().getCpsn_number().trim());
    }

    public void showDetailFromStit() {
         Expense exp = (Expense) this.statModel.getRowData();
        //Expense exp = (Expense) this.getStitData().getRowData();
        if (exp.getVoutype().trim().equals("0")) {
            ExpenseInputBean expense = (ExpenseInputBean) FacesUtils.getManagedBean("expinput");
            expense.setExp(exp);
            expense.getDis().setFirst(false);
            expense.getDis().setPreviouse(false);
            expense.getDis().setNext(false);
            expense.getDis().setLast(false);

            NavigationTool.getSelectedUrl("page/pay/business/person/expenseaccount.xhtml");
        }else
        if (exp.getVoutype().trim().equals("1")) {
            TravelInputBean tra = (TravelInputBean) FacesUtils.getManagedBean("trainput");
            tra.setExp(exp);
            tra.getDis().setFirst(false);
            tra.getDis().setPreviouse(false);
            tra.getDis().setNext(false);
            tra.getDis().setLast(false);
            NavigationTool.getSelectedUrl("page/pay/business/person/tripaccount.xhtml");
        }else
        if (exp.getVoutype().trim().equals("3")) {
            LoanInputBean loanbill = (LoanInputBean) FacesUtils.getManagedBean("loan");
            loanbill.setExp(exp);
            loanbill.getDis().setFirst(false);
            loanbill.getDis().setPreviouse(true);
            loanbill.getDis().setNext(true);
            loanbill.getDis().setLast(false);
            loanbill.setShowAble(true);
            loanbill.setSaveOrNot(true);
            NavigationTool.getSelectedUrl("page/pay/business/person/loanbill.xhtml");
        }
        FacesContext.getCurrentInstance().renderResponse();
        //return "main";
    }

    public void queryForStit() {
        String inParam = "";
        for (String type : voutypes) {
            inParam += type + ",";
        }
        if (inParam != null && inParam.length() > 0) {
            inParam = inParam.substring(0, inParam.length() - 1);
        }
        //System.out.println("inParam:" + inParam);
        Map map = new HashMap();
        map.put("cpsn_number", curPerNum().trim());
        map.put("numDateStart", qlb.getNumDateStart());
        map.put("numDateEnd", qlb.getNumDateEnd());
        map.put("typeList", inParam);
        exp = (Expense) IbatisTemplate.query("loanbill.getPsnTotal",map);
        statlist = IbatisTemplate.queryForList("loanbill.ForStit", map);
        statlist.add(exp);
        statModel.setWrappedData(statlist);
        //totalByPsn = CommonOperationImpl.totalByPsn(map);
        //System.out.println("Size:" + totalByPsn);
        FacesContext.getCurrentInstance().renderResponse();
    }
    public void queryForStitTotal() {
        String inParam = "";
        for (String type : voutypes) {
            inParam += type + ",";
        }
        if (inParam != null && inParam.length() > 0) {
            inParam = inParam.substring(0, inParam.length() - 1);
        }
        //System.out.println("inParam:" + inParam);
        Map map = new HashMap();
        map.put("cpsn_number", curPerNum().trim());
        map.put("numDateStart", qlb.getNumDateStart());
        map.put("numDateEnd", qlb.getNumDateEnd());
        map.put("typeList", inParam);
        totalByPsn = CommonOperationImpl.totalByPsn(map);     
        FacesContext.getCurrentInstance().renderResponse();
    }

    public void statisticsForPay() {
        String inParam = "";
        for (String type : voutypes) {
            inParam += type + ",";
        }
        if (inParam != null && inParam.length() > 0) {
            inParam = inParam.substring(0, inParam.length() - 1);
        }

        Map map = new HashMap();
        map.put("numDateStart", qlb.getNumDateStart());
        map.put("numDateEnd", qlb.getNumDateEnd());
        map.put("typeList", inParam);
        exp = (Expense) IbatisTemplate.query("loanbill.getTotal",map);
        statlist = IbatisTemplate.queryForList("loanbill.statisForPay", map);
        statlist.add(exp);
        statModel.setWrappedData(statlist);        
        //totalByPsn = CommonOperationImpl.payStatis(map);
        //System.out.println("Size:" + totalByPsn.size());
        FacesContext.getCurrentInstance().renderResponse();
    }
     public void statisticsForPayTotal() {
        String inParam = "";
        for (String type : voutypes) {
            inParam += type + ",";
        }
        if (inParam != null && inParam.length() > 0) {
            inParam = inParam.substring(0, inParam.length() - 1);
        }

        Map map = new HashMap();
        map.put("numDateStart", qlb.getNumDateStart());
        map.put("numDateEnd", qlb.getNumDateEnd());
        map.put("typeList", inParam);
        totalByPsn = CommonOperationImpl.payStatis(map);
        //System.out.println("Size:" + totalByPsn.size());
        FacesContext.getCurrentInstance().renderResponse();
    }

    public void exportExcel() {
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("个人单据统计表");
        String[] excelColumns = initTabeleHeader();
        List<Expense> rows = statlist;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("个人单据统计表");
        HSSFRow headerRow = demoSheet.createRow(0);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((int) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 1;
        for (Expense row : rows) {
            createTableRow(demoWorkBook, demoSheet, row, rowIndex);
            rowIndex++;
        }
        StitBean.exportExcel(demoWorkBook, demoSheet);

    }

    public void cancel() {
        qlb = new QueryVouchers();
    }

    //初始化表头信息
    public String[] initTabeleHeader() {
        String[] excelColumns = {"单据编号", "单据类别", "单据日期", "（借款/报销）金额","扣税额", "核定金额", "还款金额", "待支付金额", "支付金额"};
        return excelColumns;
    }

    public static void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, Expense epe, int rowIndex) {
        //创建第rowIndex行
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));//格式

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(PayCodeConstants.convertString(epe.getNumber()));
        cell = row.createCell(1);
        cell.setCellValue(PayCodeConstants.convertString(PayCodeConstants.functionVoutype().get(epe.getVoutype())));
        cell = row.createCell(2);
        cell.setCellValue(PayCodeConstants.convertDate(epe.getNumDate()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(3);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getTotal()));
        cell = row.createCell(4);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getTaxAmount()));
        cell = row.createCell(5);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getCheckaccount()));
        cell = row.createCell(6);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getRepayment()));
        cell = row.createCell(7);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getRemain()));
        cell = row.createCell(8);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getPayaccount()));
    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void exportExcelTotal() {
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("统计表");
        String[] excelColumns = initTotalTableHeader();
        List<Expense> rows = totalByPsn;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("统计表");
        HSSFRow headerRow = demoSheet.createRow(0);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((int) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 1;
        for (Expense row : rows) {
            createTotalTableRow(demoWorkBook, demoSheet, row, rowIndex);
            rowIndex++;
        }
        StitBean.exportExcel(demoWorkBook, demoSheet);

    }
     //初始化表头信息
    public String[] initTotalTableHeader() {
        String[] excelColumns = { "单据类型", "单据金额", "扣税金额", "还款金额", "待支付金额","核定金额","支付金额"};
        return excelColumns;
    }
    public static void createTotalTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, Expense epe, int rowIndex) {
        //创建第rowIndex行
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));//格式

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);        
        cell.setCellValue(PayCodeConstants.convertString(PayCodeConstants.functionVoutype().get(epe.getVoutype())));
        cell = row.createCell(1);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getTotal()));
        cell = row.createCell(2);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getTaxAmount()));
        cell = row.createCell(3);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getRepayment()));
        cell = row.createCell(4);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getRemain()));
        cell = row.createCell(5);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getCheckaccount()));              
        cell = row.createCell(6);
        cell.setCellValue(PayCodeConstants.convertDouble(epe.getPayaccount()));
    }

    public List<Expense> getStatlist() {
        return statlist;
    }

    public void setStatlist(List<Expense> statlist) {
        this.statlist = statlist;
    }

    public Stit getStit() {
        return stit;
    }

    public void setStit(Stit stit) {
        this.stit = stit;
    }

    public UIDataTable getStitData() {
        return stitData;
    }

    public void setStitData(UIDataTable stitData) {
        this.stitData = stitData;
    }

   
    public QueryVouchers getQlb() {
        return qlb;
    }

    public void setQlb(QueryVouchers qlb) {
        this.qlb = qlb;
    }

    public List<String> getVoutypes() {
        return voutypes;
    }

    public void setVoutypes(List<String> voutypes) {
        this.voutypes = voutypes;
    }

    public List<Expense> getTotalByPsn() {
        return totalByPsn;
    }

    public void setTotalByPsn(List<Expense> totalByPsn) {
        this.totalByPsn = totalByPsn;
    }

    public Expense getExp() {
        return exp;
    }

    public void setExp(Expense exp) {
        this.exp = exp;
    }  

    public DataModel getStatModel() {
        return statModel;
    }

    public void setStatModel(DataModel statModel) {
        this.statModel = statModel;
    }

     //
    private String curPerNum() {
        return LoginParam.getPer().getCpsn_number();
    }

}
